import pymysql


class UtilTest:
    # 创建链接
    __connect = None
    __cursor = None

    @classmethod
    def __create_connect(cls):
        if cls.__connect is None:
            cls.__connect = pymysql.connect(host='localhost', user='root', password='root', database='books',
                                            autocommit=False)
        return cls.__connect

    # 创建游标
    @classmethod
    def __create_course(cls):
        if cls.__cursor is None:
            cls.__cursor = cls.__create_connect().cursor()
        return cls.__cursor

    # 执行sql
    @classmethod
    def execute(cls, sql):
        try:
            cr = cls.__create_course()
            cr.execute(sql)
            if sql.split()[0].lower() == 'select':
                return cr.fetchall()
            else:
                cls.__connect.commit()
                return cr.rowcount
        except Exception as e:
            cls.__connect.robllback()
            print(e)
        finally:
            cls.__close_course()
            cls.__close_connect()

    # 关闭游标
    @classmethod
    def __close_course(cls):
        if cls.__cursor:
            cls.__cursor.close()
            cls.__cursor = None

    # 关闭链接
    @classmethod
    def __close_connect(cls):
        if cls.__connect:
            cls.__connect.close()
            cls.__connect = None


if __name__ == '__main__':
    sql = 'select * from t_book'
    # sql = 'insert into t_book(id,title,pub_date) values(4,"西游记","1986-01-01")'
    # sql = 'update t_book set title="东游记" where title="西游记"'
    # sql = 'insert into t_hero (id,name,gender,description,book_id) values(1,"郭靖",1,"降龙十八掌",4);'
    # sql = 'delete from t_hero where id = 6'
    # sql = 'delete from t_book where id=4'
    result = UtilTest.execute(sql)
    print(result)
